# -*- coding: utf-8 -*-

import xlrd
import sqlite3
from sqlalchemy import create_engine
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy.sql import select
from sqlalchemy.sql import and_
import os

if (os.path.exists('Inscritos_2010-2011-bdd.db')):
    os.unlink('Inscritos_2010-2011-bdd.db')

# Função que cria a conexão à base de dados através do SQLAlchemy
engine = create_engine('sqlite:///Inscritos_2010-2011-bdd.db', echo=True)
metadata = MetaData(bind=engine)

listbdd = []	# Lista que guarda os dados lidos do .xls para depois serem gravados na bdd
primKey = 0		# Variável que vai incrementar a chave primária da tabela

ESTAB_ENSINO = ''
UNI_ORGANICA = ''
NIVEL_FORMACAO = ''
CURSO_SUP = ''
AREA_EDU_FOR = ''
NAME_TABLE_XLS = "Tabela 4.1"
NAME_XLS = "Inscritos_2010-2011.xls"

workbook = xlrd.open_workbook(NAME_XLS)	# Abertura do workbook com o pacote xlrd

# Lista com os nomes das colunas da tabela
listRowBdd = [
'estabelecimento','unidade','nivel','curso','area',
'9596h','9596m','9596hm',
'9697h','9697m','9697hm',
'9798h','9798m','9798hm',
'9899h','9899m','9899hm',
'9900h','9900m','9900hm',
'0001h','0001m','0001hm',
'0102h','0102m','0102hm',
'0203h','0203m','0203hm',
'0304h','0304m','0304hm',
'0405h','0405m','0405hm',
'0506h','0506m','0506hm',
'0607h','0607m','0607hm',
'0708h','0708m','0708hm',
'0809h','0809m','0809hm',
'0910h','0910m','0910hm',
'1011h','1011m','1011hm'
]

# Definição da tabela através do SQLAlchemy. O seguinte código
# é identico ao "SQL CREATE TABLE" em SQL
users_table = Table('elementsbdd', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('estabelecimento', String),
                    Column('unidade', String),
                    Column('nivel', String),
                    Column('curso', String),
                    Column('area', String),
                    Column('9596h', String),
                    Column('9596m', String),
                    Column('9596hm', String),
                    Column('9697h', String),
                    Column('9697m', String),
                    Column('9697hm', String),
                    Column('9798h', String),
                    Column('9798m', String),
                    Column('9798hm', String),
                    Column('9899h', String),
                    Column('9899m', String),
                    Column('9899hm', String),
                    Column('9900h', String),
                    Column('9900m', String),
                    Column('9900hm', String),
                    Column('0001h', String),
                    Column('0001m', String),
                    Column('0001hm', String),
                    Column('0102h', String),
                    Column('0102m', String),
                    Column('0102hm', String),
                    Column('0203h', String),
                    Column('0203m', String),
                    Column('0203hm', String),
                    Column('0304h', String),
                    Column('0304m', String),
                    Column('0304hm', String),
                    Column('0405h', String),
                    Column('0405m', String),
                    Column('0405hm', String),
                    Column('0506h', String),
                    Column('0506m', String),
                    Column('0506hm', String),
                    Column('0607h', String),
                    Column('0607m', String),
                    Column('0607hm', String),
                    Column('0708h', String),
                    Column('0708m', String),
                    Column('0708hm', String),
                    Column('0809h', String),
                    Column('0809m', String),
                    Column('0809hm', String),
                    Column('0910h', String),
                    Column('0910m', String),
                    Column('0910hm', String),
                    Column('1011h', String),
                    Column('1011m', String),
                    Column('1011hm', String)
                )
metadata.create_all()	# Criação da tabela na base de dados
con = engine.connect()	# Fazer a conexão à base de dados

# SHEET
sh = workbook.sheet_by_name(NAME_TABLE_XLS)	# Selecção folha para leitura 

checkCol = 0
# Neste ciclo é verificada qual a coluna vazia, sendo guardada na variável checkCol
for col in range(sh.ncols):
    if (sh.cell(3,col).value == ""):
        checkCol = col

# LINHAS
for rownum in range(4, sh.nrows, 1):
    '''
    Para cada linha são feitas várias verificações. As 5 primeiras colunas
    são referentes ao estabelecimento, unidade orgânica, nivel de formação
    curso superior e área de educação e formação. Caso seja encontrada a
    string 'TOTAL' o programa pára a leitura, senão, se as celulas não tiver
    vazias o seu valor é guardado nas respectivas variáveis.
    '''
    if sh.cell_value(rownum,0) == 'TOTAL':
        break
    else:
        if sh.cell_value(rownum,0) != '':
            ESTAB_ENSINO = sh.cell_value(rownum, 0).encode('latin-1')
        if sh.cell_value(rownum, 1) != '':
            UNI_ORGANICA = sh.cell_value(rownum, 1).encode('latin-1')
        if sh.cell_value(rownum,2) != '':
            NIVEL_FORMACAO = sh.cell_value(rownum, 2).encode('latin-1')
        if sh.cell_value(rownum,3) != '':
            CURSO_SUP = sh.cell_value(rownum, 3).encode('latin-1')
        if sh.cell_value(rownum,4) != '':
            AREA_EDU_FOR = sh.cell_value(rownum, 4).encode('latin-1')

    # COLUNAS
    listbdd[:] = []		# Remover todos os dados da lista
    '''
    Dentro do primeiro ciclo for, onde os dados vão ser obtidos celula a
    celula. Se o valor da celula em questão for '-', o valor guardado
    na lista será '0.0', uma vez que são dados relativos ao número de 
    inscritos. Como existem valores float, é feita a verificação através
    da função isinstance(). Se as cinco primeiras celulas forem vazias
    então será guardado na lista o valor respectivo anteriormente guardado.
    '''
    for col in range(sh.ncols):
        if (col != checkCol):
            if sh.cell(rownum,col).value == "-":
                listbdd.append(str("0.0"))
            elif isinstance(sh.cell(rownum,col).value, float):
                listbdd.append(str(sh.cell(rownum,col).value))
            else:
                if (sh.cell(rownum,col).value) == '' and col == 0:
                    listbdd.append(str(ESTAB_ENSINO))
                elif (sh.cell(rownum,col).value) == '' and col == 1:
                    listbdd.append(str(UNI_ORGANICA))
                elif (sh.cell(rownum,col).value) == '' and col == 2:
                    listbdd.append(str(NIVEL_FORMACAO))
                elif (sh.cell(rownum,col).value) == '' and col == 3:
                    listbdd.append(str(CURSO_SUP))
                elif (sh.cell(rownum,col).value) == '' and col == 4:
                    listbdd.append(str(AREA_EDU_FOR))
                else:
                    listbdd.append(str((sh.cell(rownum,col).value).encode('latin-1')))  

    strQuery = "INSERT INTO elementsbdd VALUES (%i" % primKey		# String que vai conter a querie para inserir na bdd
    # Ciclo que adiciona os valores da listbdd à strQuery
    for element in listbdd:
        element = element.replace("'", "''")	# Substituição do apostrofe para evitar erros no Insert na bdd
        strQuery += ", \'%s\'" % element.decode('latin-1')		# Junção dos valores à strQuery
    strQuery += ")"

    primKey += 1
    # FIM COLUNA
    con.execute(strQuery)
    print "\n### END - LINHA - %i ###\n" % rownum
engine.dispose()

# FIM SHEET
print "### END ###"
